



ALTER    proc sp_packing_slip
@marketingPartNumber varchar(50)
As
declare @process_step_id bigint
declare @process_type varchar(20)
declare @process_type_name varchar(20)

declare @results Table(Lot_Number varchar(20),
			Lot_ID bigint, 
			Location_ID int,
			Location varchar(20),
			Step_Name varchar(20),
			Date_In datetime,
			Qty_In int,
			--Expected_Date_Out datetime,
			Balance_Qty int)

declare @temp_lot_id int
declare @temp_process_step_id bigint
declare @temp_location_id int
declare @temp_lot_number varchar(20)
declare @temp_location varchar(20)
declare @temp_step_name varchar(20)
declare @temp_date_in datetime
declare @temp_qty_in int
declare @temp_ship_qty int
declare @temp_expected_date_out datetime
declare @temp_expected_qty_out int
declare @temp_yield float

declare @tempCursor CURSOR
declare @pfcursor_deallocate_status int
declare @partNumber varchar(50)
set @pfcursor_deallocate_status = 0

select @temp_process_step_id = ProcessStep_ID from Product where P_Marketing_Part_Number =  @marketingPartNumber
declare pfCursor CURSOR for
select *from fn_get_process_flow_by_id(@temp_process_step_id)

open pfCursor
	fetch next from pfCursor
	into @process_step_id, @process_type, @process_type_name

	while @@fetch_status = 0
	begin
		print '@Process_type_name = '+ @process_type_name
		if @process_type_name = 'Finished Goods'
		begin
			set @tempCursor = CURSOR for
			select Lot_ID, Lot_Number, Location_ID from Lot_Table where ProcessStep_ID = @process_step_id and Lot_Current_Status = 'Running'
			
			open @tempCursor
			fetch next from @tempCursor
			into @temp_lot_id, @temp_lot_number, @temp_location_id

			while @@fetch_status = 0
			begin
				select @temp_qty_in = IL_Qty_In, @temp_date_in=IL_Date_In, @temp_ship_qty=IL_Qty_Balance from Inventory_Lot where Lot_ID = @temp_lot_id
				--select @temp_ship_qty = sum(CSLNT_Qty_Pull) from Customer_Shipping_Lot_Number_Table where Lot_ID = @temp_lot_id
				if @temp_ship_qty = 0 or @temp_ship_qty is null
				begin
					--print 'Qty_In = '
					--print @temp_qty_in
					--print 'Balance = '
					--print @temp_ship_qty
					set @temp_ship_qty = @temp_qty_in
				end
				--set @temp_qty_in = (@temp_qty_in - @temp_ship_qty)*/
				--set @temp_expected_qty_out =0
				-- NOTE: expected date out should be calculated .....currently I am assigning to the date in
				--select @temp_expected_date_out = @temp_date_in
				-- select @temp_expected_date_out = dateadd(day,(select IST_Max_Time from Inventory_Step_Table where ProcessStep_ID =@process_step_id) ,@temp_date_in)
				select @temp_location = (select v.V_Name + '/'+ a.City from Location l, Vendor v, Address a where l.Location_ID = @temp_location_id and l.Vendor_ID = v.Vendor_ID and l.Address_ID = a.Address_ID)
				insert into @results(Lot_Number, Lot_ID, Location_ID, Location, Step_Name, Date_In, Qty_In, Balance_Qty) values(@temp_lot_number, @temp_lot_id, @temp_location_id, @temp_location, @process_type_name, @temp_date_in, @temp_qty_in, @temp_ship_qty)

				fetch next from @tempCursor
				into @temp_lot_id, @temp_lot_number, @temp_location_id
			end
			close @tempCursor
			deallocate @tempCursor
			set @pfcursor_deallocate_status = 1
		end
		/*else if @process_type_name = 'Mark & Pack'
		begin
			set @tempCursor = CURSOR for
			select Lot_ID, Lot_Number, Location_ID from Lot_Table where ProcessStep_ID = @process_step_id and Lot_Current_Status = 'Running'
			
			open @tempCursor
			fetch next from @tempCursor
			into @temp_lot_id, @temp_lot_number, @temp_location_id

			while @@fetch_status = 0
			begin
				select @temp_qty_in = IL_Qty_In, @temp_date_in=IL_Date_In from Inventory_Lot where Lot_ID = @temp_lot_id

				select @temp_yield = (select MPT_Target_Yield from Mark_Pack_Table where ProcessStep_ID = @process_step_id)
				select @temp_expected_qty_out = convert(int, @temp_qty_in * @temp_yield)

				select @temp_expected_date_out = dateadd(day,(select PSLT_Cycle_Time_Normal from Process_Step_Location_Table where ProcessStep_ID =@process_step_id and Location_ID = @temp_location_id) ,@temp_date_in)
				select @temp_location = (select v.V_Name + '/'+ a.City from Location l, Vendor v, Address a where l.Location_ID = @temp_location_id and l.Vendor_ID = v.Vendor_ID and l.Address_ID = a.Address_ID)
				insert into @results(Lot_Number, Lot_ID, Location_ID, Location, Step_Name, Date_In, Qty_In, Expected_Date_Out, Expected_Qty_Out) values(@temp_lot_number, @temp_lot_id, @temp_location_id, @temp_location, @process_type_name, @temp_date_in, @temp_qty_in, @temp_expected_date_out, @temp_expected_qty_out)

				fetch next from @tempCursor
				into @temp_lot_id, @temp_lot_number, @temp_location_id
			end
			close @tempCursor
			deallocate @tempCursor

		end
		else if @process_type_name = 'Class Store'
		begin
			set @tempCursor = CURSOR for
			select Lot_ID, Lot_Number, Location_ID from Lot_Table where ProcessStep_ID = @process_step_id and Lot_Current_Status = 'Running'
			
			open @tempCursor
			fetch next from @tempCursor
			into @temp_lot_id, @temp_lot_number, @temp_location_id

			while @@fetch_status = 0
			begin
				select @temp_qty_in = IL_Qty_In, @temp_date_in=IL_Date_In from Inventory_Lot where Lot_ID = @temp_lot_id
				select @temp_expected_qty_out = @temp_qty_in
				-- NOTE: expected date out should be calculated .....currently I am assigning to the date in
				select @temp_expected_date_out = @temp_date_in
				-- select @temp_expected_date_out = dateadd(day,(select IST_Max_Time from Inventory_Step_Table where ProcessStep_ID =@process_step_id) ,@temp_date_in)
				select @temp_location = (select v.V_Name + '/'+ a.City from Location l, Vendor v, Address a where l.Location_ID = @temp_location_id and l.Vendor_ID = v.Vendor_ID and l.Address_ID = a.Address_ID)
				insert into @results(Lot_Number, Lot_ID, Location_ID, Location, Step_Name, Date_In, Qty_In, Expected_Date_Out, Expected_Qty_Out) values(@temp_lot_number, @temp_lot_id, @temp_location_id, @temp_location, @process_type_name, @temp_date_in, @temp_qty_in, @temp_expected_date_out, @temp_expected_qty_out)

				fetch next from @tempCursor
				into @temp_lot_id, @temp_lot_number, @temp_location_id
			end
			close @tempCursor
			deallocate @tempCursor

		end
		else if @process_type_name = 'TEST'
		begin
			set @tempCursor = CURSOR for
			select Lot_ID, Lot_Number, Location_ID from Lot_Table where ProcessStep_ID = @process_step_id and Lot_Current_Status = 'Running'
			
			open @tempCursor
			fetch next from @tempCursor
			into @temp_lot_id, @temp_lot_number, @temp_location_id

			while @@fetch_status = 0
			begin
				select @temp_qty_in = FTL_Qty_In, @temp_date_in=FTL_Date_In from Final_Test_Lot where Lot_ID = @temp_lot_id

				select @temp_yield = (select FTT_Target_Yield from Final_Test_Table where ProcessStep_ID = @process_step_id)
				select @temp_expected_qty_out = convert(int, (@temp_qty_in * @temp_yield)/100)

				select @temp_expected_date_out = dateadd(day,(select PSLT_Cycle_Time_Normal from Process_Step_Location_Table where ProcessStep_ID =@process_step_id and Location_ID = @temp_location_id) ,@temp_date_in)
				select @temp_location = (select v.V_Name + '/'+ a.City from Location l, Vendor v, Address a where l.Location_ID = @temp_location_id and l.Vendor_ID = v.Vendor_ID and l.Address_ID = a.Address_ID)
				insert into @results(Lot_Number, Lot_ID, Location_ID, Location, Step_Name, Date_In, Qty_In, Expected_Date_Out, Expected_Qty_Out) values(@temp_lot_number, @temp_lot_id, @temp_location_id, @temp_location, @process_type_name, @temp_date_in, @temp_qty_in, @temp_expected_date_out, @temp_expected_qty_out)

				fetch next from @tempCursor
				into @temp_lot_id, @temp_lot_number, @temp_location_id
			end
			close @tempCursor
			deallocate @tempCursor

			select @pfcursor_deallocate_status = 1
		end*/
		else
		begin
			if @pfcursor_deallocate_status = 1
			begin
				--deallocate @tempCursor
				
				close pfCursor
				deallocate pfCursor
				select *from @results
				return
			end
		end
		
		fetch next from pfCursor
		into @process_step_id, @process_type, @process_type_name
	end

close pfCursor
deallocate pfCursor




